insert overwrite table res_vipcoupon_discsource
select from_unixtime(updated,'yyyy-MM-dd')as days,enterprise_id,
sum(case when tactics_type = 'score' then 1 else 0 end) as score_allmoney,
sum(case when tactics_type = 'order' then 1 else 0 end) as order_allmoney,
sum(case when tactics_type = 'reissued' then 1 else 0 end) as reissued_allmoney,
sum(case when tactics_type like 'sunny%' then 1 else 0 end) as other_allmoney, 

sum(case when tactics_type = 'score' and coupon_statue = 3 then 1 else 0 end) as score_usedmoney,
sum(case when tactics_type = 'order' and coupon_statue = 3 then 1 else 0 end) as order_usedmoney,
sum(case when tactics_type = 'reissued' and coupon_statue = 3 then 1 else 0 end) as reissued_usedmoney,
sum(case when tactics_type like 'sunny%' and coupon_statue = 3 then 1 else 0 end) as other_usedmoney
from coupon_info where coupon_type = 2 group by from_unixtime(updated,'yyyy-MM-dd'),enterprise_id ;


insert overwrite table res_vipcoupon_source
select from_unixtime(updated,'yyyy-MM-dd')as days,enterprise_id,coupon_type,
sum(case when tactics_type = 'score' then discount_amount else 0 end) as score_allmoney,
sum(case when tactics_type = 'order' then discount_amount else 0 end) as order_allmoney,
sum(case when tactics_type = 'reissued' then discount_amount else 0 end) as reissued_allmoney,
sum(case when tactics_type like 'sunny%' then discount_amount else 0 end) as other_allmoney, 

sum(case when tactics_type = 'score' and coupon_statue = 3 then actual_amoun else 0 end) as score_usedmoney,
sum(case when tactics_type = 'order' and coupon_statue = 3 then actual_amoun else 0 end) as order_usedmoney,
sum(case when tactics_type = 'reissued' and coupon_statue = 3 then actual_amoun else 0 end) as reissued_usedmoney,
sum(case when tactics_type like 'sunny%' and coupon_statue = 3 then actual_amoun else 0 end) as other_usedmoney
from coupon_info where coupon_type !=99 group by from_unixtime(updated,'yyyy-MM-dd'),enterprise_id,coupon_type ;

insert overwrite table res_vipcoupon_source
select days,city,coupon_type,sum(score_allmoney),
sum(order_allmoney),sum(reissued_allmoney),sum(other_allmoney),
sum(score_usedmoney),sum(order_usedmoney),sum(reissued_usedmoney),sum(other_usedmoney) from
(select * from res_vipcoupon_source
union all
select * from res_vipcoupon_source_history
) tmp group by days,city,coupon_type;



insert overwrite table res_vipcoupon_oilnotoil
select from_unixtime(updated,'yyyy-MM-dd'),enterprise_id,tactics_type,coupon_type,
sum(case when coupon_type = 1 then discount_amount else 0 end) as oil_allmoney,
sum(case when coupon_type = 3 then discount_amount else 0 end) as notoil_allmoney,

sum(case when (coupon_type = 1 or coupon_type = 2) and coupon_statue = 3 then actual_amoun else 0 end) as oil_usedmoney,
sum(case when coupon_type = 3 and coupon_statue = 3 then actual_amoun else 0 end) as notoil_usedmoney
from coupon_info where coupon_type !=99 group by from_unixtime(updated,'yyyy-MM-dd'),enterprise_id,tactics_type,coupon_type;

insert overwrite table res_vipcoupon_oilnotoil
select days,city,tactics_type,coupon_type,sum(oil_allmoney),
sum(notoil_allmoney),sum(oil_usedmoney),sum(notoil_usedmoney) from
(select * from res_vipcoupon_oilnotoil
union all
select * from res_vipcoupon_oilnotoil_history) tmp
group by days,city,tactics_type,coupon_type;


insert overwrite table res_vipcoupon_nature
select from_unixtime(updated,'yyyy-MM-dd'),enterprise_id,tactics_type,coupon_type,
sum(case when coupon_type = 1 or coupon_type = 3 then discount_amount else 0 end) as off_allmoney,
sum(case when coupon_type = 2 then discount_amount else 0 end) as discount_allmoney,

sum(case when (coupon_type = 1 or coupon_type = 3) and coupon_statue = 3 then actual_amoun else 0 end) as off_usedmoney,
sum(case when coupon_type = 2 and coupon_statue = 3 then actual_amoun else 0 end) as discount_usedmoney
from coupon_info where coupon_type !=99 group by from_unixtime(updated,'yyyy-MM-dd'),enterprise_id,tactics_type,coupon_type ;




insert overwrite table res_coupon_send
select a.id,a.coupon_title,b.tactics_title,
from_unixtime(a.updated,'yyyy-MM-dd'),from_unixtime(a.updated,'yyyy-MM-dd'),
case 
when a.tactics_type = 'order' then '活动赠送'
when a.tactics_type = 'reissued' then '人工赠送'
when a.tactics_type = 'score' then '积分兑换'
else '其他活动发放' end as tactics_type  ,
case
when a.coupon_type = 1 then '燃油满减券'
when a.coupon_type = 2 then '燃油折扣券'
when a.coupon_type = 3 then '非油满减券'
else null end as coupon_type,
a.discount_amount,a.use_shop_id,
case
when a.coupon_statue = 1 then '可用'
when a.coupon_statue = 3 then '已使用'
when a.coupon_statue = 9 then '已过期'
else null end as coupon_statue
from coupon_info as a
join coupon_send_tactics as b
on a.send_tactics_id = b.id;

insert overwrite table res_coupon
select id,
case 
when enterprise_id = 5 then '承德'
when enterprise_id = 6 then '北京'
else null end as enterprise_id,
coupon_title,from_unixtime(updated,'yyyy-MM-dd'),from_unixtime(updated,'yyyy-MM-dd'),
tactics_type,
case
when coupon_type = 1 then '燃油满减券'
when coupon_type = 2 then '燃油折扣券'
when coupon_type = 3 then '非油满减券'
else null end as coupon_type,
discount_amount,use_shop_id,
case
when coupon_statue = 1 then '可用'
when coupon_statue = 3 then '已使用'
when coupon_statue = 9 then '已过期'
else null end as coupon_statue
from coupon_info where coupon_type != 99;



insert overwrite table res_wechatmall_status
select from_unixtime(A.create_time,'yyyy-MM-dd')as day,B.station_name,
sum(case when A.store_id = 1 and A.status = -1 THEN weight ELSE 0 END) AS refunded_number,
sum(case when A.store_id = 1 and A.status = -1 THEN amount_point ELSE 0 END) AS refunded_point,
sum(case when A.store_id = 1 and A.status = 0 THEN weight ELSE 0 END) AS notpay_number,
sum(case when A.store_id = 1 and A.status = 0 THEN amount_point ELSE 0 END) AS notpay_point,
sum(case when A.store_id = 1 and A.status = 2 THEN weight ELSE 0 END) AS tosend_number,
sum(case when A.store_id = 1 and A.status = 2 THEN amount_point ELSE 0 END) AS tosend_point,
sum(case when A.store_id = 1 and A.status = 7 THEN weight ELSE 0 END) AS paid_number,
sum(case when A.store_id = 1 and A.status = 7 THEN amount_point ELSE 0 END) AS paid_point,
sum(case when A.store_id = 1 and A.status = 8 THEN weight ELSE 0 END) AS cancel_number,
sum(case when A.store_id = 1 and A.status = 8 THEN amount_point ELSE 0 END) AS cancel_point
from es_order as A
join es_member_store_child as B
on A.child_store_id =  B.child_store_id
group by from_unixtime(A.create_time,'yyyy-MM-dd'),B.station_name;

insert overwrite table res_wechatmall_top
select from_unixtime(A.create_time,'yyyy-MM-dd')as day,C.station_name,B.name,sum(A.weight),sum(A.amount_point)
from es_order as A 
join es_order_items as B
on A.status = 7 and A.order_id = B.order_id
join es_member_store_child as C
on A.child_store_id =  C.child_store_id
group by from_unixtime(A.create_time,'yyyy-MM-dd'),C.station_name,B.name;

insert overwrite table res_wechatmall_topcoupon
select from_unixtime(updated,'yyyy-MM-dd')as day,coupon_title,count(id) from coupon_info
where tactics_type = 'score'
group by from_unixtime(updated,'yyyy-MM-dd'),coupon_title;

insert overwrite table res_wechatmall_topall
select from_unixtime(A.create_time,'yyyy-MM-dd')as day,B.name,sum(A.weight)
from es_order as A 
join es_order_items as B
on A.status = 7 and A.order_id = B.order_id
group by from_unixtime(A.create_time,'yyyy-MM-dd'),B.name ;

insert overwrite table res_wechatmall_topall
select * from res_wechatmall_topall
union all select * from res_wechatmall_topcoupon;


insert overwrite table res_vipcoupon_ladder
select from_unixtime(a.updated,'yyyy-MM-dd')as day, a.use_shop_id,SUM(b.actual_amoun)
from coupon_info  as a
join coupon_info_ext as b
on a.id = b.coupon_id
where coupon_statue = 3 and a.coupon_type = 99
group by A.use_shop_id,from_unixtime(A.updated,'yyyy-MM-dd');

insert overwrite table mid_vipCoupon
select A.daya,A.suma,B.used
from
(select from_unixtime(updated,'yyyy-MM-dd')as daya,sum(discount_amount)as suma
from coupon_info
group by from_unixtime(updated,'yyyy-MM-dd'))as A
join
(select from_unixtime(updated,'yyyy-MM-dd')as dayb,sum(discount_amount)as used
from coupon_info where coupon_statue = 3
group by from_unixtime(updated,'yyyy-MM-dd'))as B
on A.daya=B.dayb;

insert overwrite table res_vipCoupon select days,allMoney, if(usedMoney is null,0,usedMoney) from mid_vipCoupon;


insert overwrite table mid_vipCouponRate
select from_unixtime(unix_timestamp(),'yyyy-MM-dd'),coupon_statue,sum(discount_amount)
from coupon_info_report group by coupon_statue;


insert overwrite table res_vipCouponrate select date,couponstatus,if(couponmoney is null,0,couponmoney) from mid_vipCouponRate;

insert OVERWRITE table res_mid_couponTactic
select y.month,y.send_tactics_id,y.tactics_title,y.count,z.count
from 
(select  A.send_tactics_id,B.tactics_title,count(A.send_tactics_id)as count
from coupon_info as A
 join coupon_send_tactics as B
ON B.id = A.send_tactics_id and A.send_tactics_id is not null and A.coupon_statue = 3
group by A.send_tactics_id,B.tactics_title)z
right join 
(select substr(B.release_time_start,1,10)as month, A.send_tactics_id,B.tactics_title,count(A.send_tactics_id) as count
from coupon_info as A
 join coupon_send_tactics as B
ON B.id = A.send_tactics_id and A.send_tactics_id is not null 
group by A.send_tactics_id,B.tactics_title,substr(B.release_time_start,1,10))y
on z.send_tactics_id = y.send_tactics_id;

insert OVERWRITE table res_coupontactic
select month,send_tactics_id,tacticsTitle,allNumber,if(usedNumber is null,0,usedNumber)
from res_mid_couponTactic;

insert overwrite table res_vipcoupon_station 
select from_unixtime(updated,'yyyy-MM-dd')as day,use_shop_id,
sum(case when coupon_type = 1 and tactics_type = 'score' then discount_amount else 0 end) as oil_score_usedmoney,
sum(case when coupon_type = 1 and tactics_type = 'reissued' then discount_amount else 0 end ) as oil_reissued_usedmoney,
sum(case when coupon_type = 1 and tactics_type = 'order' then discount_amount else 0 end) as oil_order_usedmoney,
sum(case when coupon_type = 1 and tactics_type regexp '[0-9]{1,}' then discount_amount else 0 end) as oil_hfive_usedmoney,

sum(case when coupon_type = 3 and tactics_type = 'score' then discount_amount else 0 end) as notoil_score_usedmoney,
sum(case when coupon_type = 3 and tactics_type = 'reissued' then discount_amount else 0 end ) as notoil_reissued_usedmoney,
sum(case when coupon_type = 3 and tactics_type = 'order' then discount_amount else 0 end) as notoil_order_usedmoney,
sum(case when coupon_type = 3 and tactics_type regexp '[0-9]{1,}' then discount_amount else 0 end) as notoil_hfive_usedmoney,

sum(case when coupon_type = 2 and tactics_type = 'order' then actual_amoun else 0 end) as oil_order_usednum,
sum(case when coupon_type = 2 and tactics_type != 'order' then actual_amoun else 0 end) as oil_order_usednum

from coupon_info where coupon_statue = 3 group by from_unixtime(updated,'yyyy-MM-dd'),use_shop_id ;
